package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import bean.DormitoryBean;
import bean.UserBean;

public class DormitoryDao {
	public DormitoryBean getDormitory(int id) throws SQLException, ClassNotFoundException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("select * from dormitory where dormitoryid=?");
		ps.setInt(1, id);
		ResultSet result = ps.executeQuery();
		result.next();
		DormitoryBean dormitory = new DormitoryBean();
		dormitory.setId(result.getInt(1));
		dormitory.setName(result.getString(2));
		
		result.close();
		ps.close();
		conn.close();
		return dormitory;
	}
	public List<DormitoryBean> getFriendDormitory(int id) throws ClassNotFoundException, SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("select dormitoryid, dormitoryname from dormitory ,((select dormitoryid1 from friend_dormitory where dormitoryid2=?) union all (select dormitoryid2 from friend_dormitory where dormitoryid1=?))u where dormitory.dormitoryid=u.dormitoryid1");
		ps.setInt(1, id);
		ps.setInt(2, id);
		ResultSet result = ps.executeQuery();
		List<DormitoryBean> list = new ArrayList<>();
		DormitoryBean dormitory;
		while(result.next()) {
			dormitory = new DormitoryBean();
			dormitory.setId(result.getInt("dormitoryid"));
			dormitory.setName(result.getString("dormitoryname"));
			list.add(dormitory);
		}
		return list;
	}
	public List<UserBean> getMember(int id) throws ClassNotFoundException, SQLException {
		Connection conn = DBUtil.getConnection();
		PreparedStatement ps = conn.prepareStatement("select * from user where dormitoryid=?");
		ps.setInt(1, id);
		ResultSet result = ps.executeQuery();
		List<UserBean> list = new ArrayList<>();
		UserBean user;
		while(result.next()) {
			user = new UserBean();
			user.setId(result.getInt(1));
			user.setName(result.getString(2));
			user.setTelephone(result.getString(4));
			user.setDormitoryId(result.getInt(5));
			list.add(user);
		}
		return list;
	}
}
